<?php 
$req_path="./";
include_once($req_path."include/ini.php");
require_once($req_path."include/db.php");

class M_customer_history extends DB{
	private $dbConn;
	private $rowCount="0";
	private $ID="0";
	private $totalpage="0";
	private $sqlSelect ="select * from tb_customer_history where 1=1";
	
	function M_customer_history(){
		parent::__construct();
	}
	function getListPage(){
		global $list_page;
		return $list_page;
	}
	//For customer info
	function getDataInfo($id){
		$sqlscript= "select ID,Address, Code , FirstName, LastName, DATE_FORMAT(BirthDay,'%d/%m/%Y') as BirthDay "
					.",Telephone , DATE_FORMAT(LastedAccess,'%d/%m/%Y') as LastedAccess , Email";
		$sqlscript.= " from tb_customer where 1=1 and IsDeleted=0 ";
		$sqlscript.= " and ID='".$id."'";
		$result = $this->ExecuteData($sqlscript);
		return $result->FetchRow();
	}	
	function getPrintData($id,$cust){
		$id = str_replace('|',',',$id);
		$sql = "SELECT FirstName, LastName , DATE_FORMAT( NOW(),'%d/%m/%Y') CDate from tb_customer where ID=".$cust;
		$result = $this->ExecuteData($sql);
		$header = $result->FetchRow();
	
		$sql = "SELECT p.ProductName ,  DATE_FORMAT( cc.ExpireDate,'%d/%m/%Y') ExpireDate , cc.Remain   
				FROM tb_customer_service cc
				join tb_product p on p.ID = cc.ProductID
				where 1=1 and cc.IsCancel = 0 and ExpireDate >= NOW() and cc.id IN (".$id.")";
		
		$result = $this->ExecuteData($sql);
		$arHeader=array();
		while ($row=$result->FetchRow()){
			array_push($arHeader,$row);
		}
		//$header['script']=$sql;
		$header['Product']=$arHeader;
		
		$billNo=$this->getLastBillNo($_SESSION['bid']);
		$header['BillNo'] = $billNo;
		
		$sqlService = " SELECT p.ProductName ,  DATE_FORMAT( cc.ExpireDate,'%d/%m/%Y') ExpireDate , cc.Remain
						FROM tb_customer_service cc
						join tb_product p on p.ID = cc.ProductID
						where 1=1 and cc.IsCancel = 0  and ExpireDate >= NOW() and cc.CustID = ".$cust." and cc.id NOT IN (".$id.") ";
		$result = $this->ExecuteData($sqlService);
		$cnt=$result->RecordCount();
		$arService=array();
		while ($row=$result->FetchRow()){
			array_push($arService,$row);
		}
		$header['cnts']=$cnt;
		$header['Service'] = $arService;
		
		return $header;
	}
	function getLastBillNo($bid){
		$sql ="select BillNo from tb_branch where ID='".$bid."'";
		
		$result = $this->ExecuteData($sql);
		
		$sqlupdate="update tb_branch set BillNo=BillNo+1 where ID='".$bid."'";
		$this->ExecuteData($sqlupdate);
		
		$row=$result->FetchRow();
		
		return $row["BillNo"]+1;
	}
	//For customer history
	function getData($id,$pageStart=1){
		global $list_page;
		$sqlscript= "Select  p.CustID, pduct.ProductName, b.BranchName,b.ID as BID, "
				." coalesce(DATE_FORMAT( SaleDate , '%d/%m/%Y' ),'&nbsp;') Historydate  ,"
				." SUM(pd.Amount) as Amount, SUM(pd.Price) as Price, SUM(pd.Discount) as Discount, SUM(pd.Total) as Total , "
				." u.Unit "
				." From tb_pos p "
				." left join tb_pos_detail pd on p.id=pd.Pos_id "
				." left join tb_product pduct on pduct.ID=pd.Product "
				." left join tb_branch b on b.ID=p.Branch "
				." left join tb_units u on u.ID=pduct.MainUnit "
				." Where 1=1 "
				." and pd.IsCancel=0 "
				." and p.IsCancel=0 "
				." and p.CustID=".$id
				." Group by   p.CustID ,pduct.ProductName, b.BranchName,b.ID  ,DATE_FORMAT( SaleDate , '%d/%m/%Y' ) "
				." order by SaleDate DESC";
				
		$result = $this->ExecuteData($sqlscript);
		$rows=$result->RecordCount();
		
		//Page limit
		$sqlscript.= " limit ". ($pageStart-1)*$list_page. ", " . $list_page;
		//paging
		$result = $this->ExecuteData($sqlscript);		
	
		$totalpage=1;
		if ($rows>0){
			$rt = $rows%$list_page;
			
			if($rt!=0) {
				$totalpage = floor($rows/$list_page)+1;
			}
			else {
				$totalpage = floor($rows/$list_page);
			}
		}
		
		$this->rowCount=$rows;
		$this->totalpage=$totalpage;	
		
		return $result;
	}
	function getSetTreatmentInCourse($sid ){
		$sql=" SELECT  pd.SubProductID as ID
				FROM tb_customer_service cs
				join tb_product_details pd on cs.ProductID=pd.ProductID
				where 1=1 and cs.IsCancel = 0 and cs.id=".$sid;
				
		$result = $this->ExecuteData($sql);	
		return $result;
	}
	function getProductDetail($sid ,$bid , $amt=1){
		$sql="
			SELECT pp.ID, Sum(pdd.Amount) as Amount
			FROM tb_customer_service cs
			join tb_product_details pd on cs.ProductID=pd.ProductID
			join tb_product p on p.ID=pd.SubProductID
			join tb_product_details pdd on pdd.ProductID=p.ID
			join tb_product pp on pdd.SubProductID = pp.ID
			where 1=1 and cs.IsCancel = 0  and cs.id=".$sid." Group by pp.ID ";
		$cnt=0;
		$result = $this->ExecuteData($sql);
		$cnt=$result->RecordCount();
		
		while ($row=$result->FetchRow()){
			$sql = " select p.ProductName  , coalesce(s.Amount ,0) as Amount 
					from  tb_product p 
					left join  tb_stock_product s on p.ID=s.ProductID and s.BranchID=".$bid." and s.Amount >= ".($row['Amount'] * $amt) ."
					 join tb_units u on u.ID= CASE WHEN p.SubUnit='' or p.SubUnit=0 Then p.MainUnit else p.SubUnit End
					where p.IsDeleted=0 and  p.ID=".$row['ID'] ;
				
			$result2 = $this->ExecuteData($sql);
			$rows2=$result2->FetchRow();
			 $rows2['count']=$cnt;
				if ($rows2['Amount'] > 0){
					$rows2['result']=1;
				}else{
					$rows2['result']=0;
					break;
				}
  
			//$rows2['count']=$cnt;
		}
		
		return $rows2;
		
	}
	function getAccessService($id){
		$sql="select h.id , b.BranchName, h.Remark, "
			." coalesce(DATE_FORMAT( h.ApprovedDate , '%d/%m/%Y' ),'&nbsp;') _ApprovedDate , h.ApprovedBy , h.Amount  "
			." from tb_customer_service_history h "
			." join tb_branch b on b.ID=h.BranchID "
			." where h.SID=".$id." order by ApprovedDate desc ";
			
		$result = $this->ExecuteData($sql);	
		$rows=$result->RecordCount();
	
		$this->rowCount=$rows;
			
		return $result;
	}
	function delService($id,$rid,$amt=1){
		$sqlInsert="delete from tb_customer_service_history where id=".$rid;
		$this->ExecuteData($sqlInsert);	
		
		$sqlUpdate="Update tb_customer_service set Remain=Remain+ ".$amt." Where id=".$id;
		$this->ExecuteData($sqlUpdate);	
			
	}
	function addService($id,$BranchID,$remark,$UserName , $amt=1){
		$sqlInsert="Insert into  tb_customer_service_history ( SID, BranchID, Remark, ApprovedDate, ApprovedBy, Amount) "
				." values ( ".$id.",".$BranchID.",'".$remark."', Now(),'".$UserName."' , ". $amt. ") ";
		$this->ExecuteData($sqlInsert);	
		
		$this->ID = $this->getLastID();
		
		$sqlUpdate="Update tb_customer_service set LastAccess=Now(), Remain=Remain- ".$amt." Where id=".$id;
		$this->ExecuteData($sqlUpdate);	
		
	
		$sqlUpdate="Update tb_customer  set LastedAccess=Now()  Where id IN ( select CustID from tb_customer_service where id= ".$id. ")";
		$this->ExecuteData($sqlUpdate);	
		
		return $this->ID;
	}
	
	function getServiceAllData($id){
		$sqlscript= "Select css.id, pduct.ProductName,css.BranchID_Sale, b.BranchName, css.Times , css.Remain , "
				." coalesce(DATE_FORMAT( css.LastAccess , '%d/%m/%Y' ),' ') _LastAccess  "
				." ,coalesce(DATE_FORMAT( css.ExpireDate , '%d/%m/%Y' ),' ') ExpiredDate  "
				." From tb_customer_service css "
				." left join tb_product pduct on pduct.ID=css.ProductID "
				." left join tb_branch b on b.ID=css.BranchID_Sale "
				." Where 1=1 and css.IsCancel = 0  and ExpireDate >= NOW() "
				." and pduct.type <> 1 "
				." and css.CustID=".$id
				." order by css.CreatedDate DESC";
				
		$result = $this->ExecuteData($sqlscript);
		$rows=$result->RecordCount();
		$this->rowCount=$rows;

		return $result;		
	}
	function getServiceData($id,$pageStart=1){
		global $list_page;
		
		$sqlscript= "Select css.id, pduct.ProductName,css.BranchID_Sale, b.BranchName, css.Times , css.Remain , "
				." coalesce(DATE_FORMAT( css.LastAccess , '%d/%m/%Y' ),' ') _LastAccess  "
				." ,coalesce(DATE_FORMAT( css.ExpireDate , '%d/%m/%Y' ),' ') ExpiredDate  "
				." ,CASE WHEN css.ExpireDate < NOW() THEN 1 ELSE 0 END checkExpire "
				." From tb_customer_service css "
				." left join tb_product pduct on pduct.ID=css.ProductID "
				." left join tb_branch b on b.ID=css.BranchID_Sale "
				." Where 1=1 and css.IsCancel = 0 "
				." and pduct.type <> 1 "
				." and css.CustID=".$id
				." order by css.CreatedDate DESC";
		//return $sqlscript;
		$result = $this->ExecuteData($sqlscript);
		 
		$rows=$result->RecordCount();
		
		//Page limit
		$sqlscript.= " limit ". ($pageStart-1)*$list_page. ", " . $list_page;
		//paging
		$result = $this->ExecuteData($sqlscript);			
		$totalpage=1;
		if ($rows>0){
			$rt = $rows%$list_page;
			
			if($rt!=0) {
				$totalpage = floor($rows/$list_page)+1;
			}
			else {
				$totalpage = floor($rows/$list_page);
			}
		}

		$this->rowCount=$rows;
		$this->totalpage=$totalpage;	
		
		return $result;
	}
	
	function getRowCount(){
		return $this->rowCount;
	}
	
	function getLastInsert(){
		return $this->ID;
	}
	function getTotalPage(){
		return $this->totalpage;
	}
  
	 
}
?>